﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Configuration;
using System.Windows.Forms;
using System.Data;
using log4net;

namespace Riel_Gestion.dao
{
    class ClienteDAO
    {
        private static readonly ILog logger = LogManager.GetLogger(typeof(FrmAgregarCliente));

        private static String SQL_INSERT = "INSERT INTO CLIENTE (CODCLIENTE, NOMBRE, CUIT, IVA, RESPONSABLE, TELEFONO, DIRECCION, EMAIL, OTROS) VALUES (?,?,?,?,?,?,?,?,?)";
        private static String SQL_SELECT = "SELECT * FROM CLIENTE ORDER BY CODCLIENTE ASC";
        private static String SQL_SELECT_BY_ID = "SELECT CODCLIENTE, NOMBRE, CUIT, IVA, RESPONSABLE, TELEFONO, DIRECCION, EMAIL, OTROS FROM CLIENTE WHERE CODCLIENTE = ?";
        private static String SQL_UPDATE = "UPDATE CLIENTE SET NOMBRE = ?, CUIT = ?, IVA = ?, RESPONSABLE = ?, TELEFONO = ?, DIRECCION = ?, EMAIL = ?, OTROS = ? WHERE CODCLIENTE = ?"; 
        private static OleDbConnection conn;
        private static OleDbCommand cmd;
        private static OleDbDataAdapter adapter;
        private static OleDbDataReader reader;

        public static void save(DTOCliente cli)
        {
            logger.Info("Save Cliente");
            try
            {
                conn = new OleDbConnection(ConfigurationManager.ConnectionStrings["rielConnectionString"].ConnectionString);
                               
                cmd = new OleDbCommand(SQL_INSERT, conn);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.AddWithValue("CODCLIENTE", cli.CodCliente);
                cmd.Parameters.AddWithValue("NOMBRE", cli.Nombre);
                cmd.Parameters.AddWithValue("CUIT", cli.Cuit);
                cmd.Parameters.AddWithValue("IVA", cli.Iva);
                cmd.Parameters.AddWithValue("RESPONSABLE", cli.Responsable);
                cmd.Parameters.AddWithValue("TELEFONO", cli.Telefono);
                cmd.Parameters.AddWithValue("DIRECCION", cli.Direccion);
                cmd.Parameters.AddWithValue("EMAIL", cli.Email);
                cmd.Parameters.AddWithValue("OTROS", cli.Otros);

                conn.Open();
                int res = cmd.ExecuteNonQuery();

            }
            catch (Exception e)
            {
                logger.Error(e.Message, e);
                FrmMain.showErrorDialog(e.Message);                
            }
            finally
            {
                if (conn != null)
                    conn.Close();
            }            
        }


        public static DataTable getList()
        {
            logger.Info("GetList Cliente");
            DataTable clientes = null;
            try
            {
                conn = conn = new OleDbConnection(ConfigurationManager.ConnectionStrings["rielConnectionString"].ConnectionString);
                cmd = new OleDbCommand(SQL_SELECT, conn);
                conn.Open();

                cmd.CommandType = CommandType.Text;
                adapter = new OleDbDataAdapter(cmd);
                clientes = new DataTable();
                adapter.Fill(clientes);
            }            
            catch (Exception e)
            {
                logger.Error(e.Message, e);
                FrmMain.showErrorDialog(e.Message);
            }
            finally
            {
                if (conn != null)
                    conn.Close();
            }
            return clientes;
        }

        public static DTOCliente getData(String idCliente)
        {
            logger.Info("GetData Cliente");
            DTOCliente cliente = null;
            try
            {
                cliente = new DTOCliente();
                conn = conn = new OleDbConnection(ConfigurationManager.ConnectionStrings["rielConnectionString"].ConnectionString);
                cmd = new OleDbCommand(SQL_SELECT_BY_ID, conn);
                conn.Open();

                cmd.CommandType = CommandType.Text;
                cmd.Parameters.AddWithValue("CODCLIENTE", idCliente);

                reader = cmd.ExecuteReader();

                if(reader.Read())
                {
                    cliente.CodCliente = long.Parse(reader["CODCLIENTE"].ToString());
                    cliente.Nombre = reader["NOMBRE"].ToString();
                    cliente.Cuit = reader["CUIT"].ToString();
                    cliente.Iva = reader["IVA"].ToString();
                    cliente.Responsable = reader["RESPONSABLE"].ToString();
                    cliente.Telefono = reader["TELEFONO"].ToString();
                    cliente.Direccion = reader["DIRECCION"].ToString();
                    cliente.Email = reader["EMAIL"].ToString();
                    cliente.Otros = reader["OTROS"].ToString();
                }
            }
            catch (Exception e)
            {
                logger.Error(e.Message, e);
                FrmMain.showErrorDialog(e.Message);
            }
            finally
            {
                if (conn != null)
                    conn.Close();
            }
            return cliente;
        }

        public static void update(DTOCliente cli)
        {
            logger.Info("Update Cliente");
            try
            {
                conn = new OleDbConnection(ConfigurationManager.ConnectionStrings["rielConnectionString"].ConnectionString);
                cmd = new OleDbCommand(SQL_UPDATE, conn);
                cmd.CommandType = CommandType.Text;                
                cmd.Parameters.AddWithValue("NOMBRE", cli.Nombre);
                cmd.Parameters.AddWithValue("CUIT", cli.Cuit);
                cmd.Parameters.AddWithValue("IVA", cli.Iva);
                cmd.Parameters.AddWithValue("RESPONSABLE", cli.Responsable);
                cmd.Parameters.AddWithValue("TELEFONO", cli.Telefono);
                cmd.Parameters.AddWithValue("DIRECCION", cli.Direccion);
                cmd.Parameters.AddWithValue("EMAIL", cli.Email);
                cmd.Parameters.AddWithValue("OTROS", cli.Otros);
                cmd.Parameters.AddWithValue("CODCLIENTE", cli.CodCliente);

                conn.Open();
                int res = cmd.ExecuteNonQuery();

            }
            catch (Exception e)
            {
                logger.Error(e.Message, e);
                FrmMain.showErrorDialog(e.Message);
            }
            finally
            {
                if (conn != null)
                    conn.Close();
            }    
        }

        public static int exist(String idCliente)
        {
            logger.Info("Exist Cliente");
            DTOCliente cliente = null;
            int ret = 0;
            try
            {
                cliente = new DTOCliente();
                conn = conn = new OleDbConnection(ConfigurationManager.ConnectionStrings["rielConnectionString"].ConnectionString);
                cmd = new OleDbCommand(SQL_SELECT_BY_ID, conn);
                conn.Open();

                cmd.CommandType = CommandType.Text;
                cmd.Parameters.AddWithValue("CODCLIENTE", idCliente);

                reader = cmd.ExecuteReader();

                if (reader.Read())
                {
                    ret = 1;                    
                }
            }
            catch (Exception e)
            {
                logger.Error(e.Message, e);
                FrmMain.showErrorDialog(e.Message);
            }
            finally
            {
                if (conn != null)
                    conn.Close();
            }
            return ret;
        }
    }
}
